import openpyxl
import insert_pal2db
from src.conn import database_connecter

# 打开Excel文件
wb = openpyxl.load_workbook('../data/幻兽帕鲁配种表.xlsx')
mydb = database_connecter.db_connector()
# 选择工作表
sheet = wb['全配种表']

# 获取表头数据
headers_row = sheet[1]
headers_column = [sheet.cell(row=i, column=1).value for i in range(2, sheet.max_row + 1)]

# 输入任意一个非表头数据
target_value = '苍炎狼'
my_cursor = mydb.cursor()
# 查找对应的表头值
matched_headers = []
for i in range(2, sheet.max_row + 1):
    for j in range(2, sheet.max_column + 1):
        # 插入数据
        sql = "INSERT INTO pw_breed (sun, parent_one, parent_two) VALUES (%s, %s, %s)"
        row_strs = insert_pal2db.get_chinese_and_other(headers_row[j - 1].value)
        column_strs = insert_pal2db.get_chinese_and_other(headers_column[i - 2])
        cell_strs = insert_pal2db.get_chinese_and_other(sheet.cell(row=i, column=j).value)
        if cell_strs[0] == '自身' or cell_strs[0] == '':
            continue
        val = (cell_strs[1], row_strs[1], column_strs[1])
        desc = (cell_strs[0], row_strs[0], column_strs[0])
        message = "儿子：{}, 父亲：{}, 母亲：{}".format(*desc)
        print(message)
        my_cursor.execute(sql, val)
        # 提交更改
mydb.commit()
# 关闭连接
mydb.close()